{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: taostd in d:\\dev\\anaconda3\\lib\\site-packages (0.0.1)\n",
      "Requirement already satisfied: cacheout>=0.13.1 in d:\\dev\\anaconda3\\lib\\site-packages (from taostd) (0.13.1)\n",
      "Requirement already satisfied: Click>=7.0 in d:\\dev\\anaconda3\\lib\\site-packages (from taostd) (7.1.2)\n",
      "Requirement already satisfied: typing>=3.7.4.3 in d:\\dev\\anaconda3\\lib\\site-packages (from taostd) (3.7.4.3)\n",
      "Requirement already satisfied: taospy>=2.1.2 in d:\\dev\\anaconda3\\lib\\site-packages (from taostd) (2.1.2)\n"
     ]
    }
   ],
   "source": [
    "!pip install taostd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "from taostd import td\n",
    "from datetime import datetime, timedelta"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "##### 使用前，请在taos客户端创建数据库：CREATE DATABASE IF NOT EXISTS test;\n",
    "##### 必须指定database；pool_size指定连接池大小，是可选的，默认是1，可以根据需要设置大小\n",
    "##### tz_offset默认是东八区：8\n",
    "##### 本例使用客户端taos.cfg配置，其它参数请参照taos.connect()方法，初始化后，项目里到处都可以用了"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "td.init_db(database=\"test\", pool_size=2, tz_offset=8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "创建stable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td.execute(\"CREATE STABLE IF NOT EXISTS meters(ts timestamp, current float, voltage float, phase int) TAGS(location nchar(20), groupId tinyint)\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "往单表插入一条数据，如果表不存在就先创建表，必须指定tags，如本例中的'location'和'groupId'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td.insert_one_with_stable(table='meter_01', stable='meters', ts=datetime.now()-timedelta(hours=1), current=0.2550, voltage=0.3542, phase=0, location='北京', groupId=0)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "往单表插入一条数据，表已经存在, 可以直接插入，不需要指定 stable和tags"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "1"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td.insert_one(table='meter_01', ts=datetime.now()-timedelta(minutes=10), current=0.3550, voltage=0.5542, phase=1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "往单张表插入多条记录"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meters = [\n",
    "    {\"ts\": datetime.now() - timedelta(minutes=1), \"current\": 0.3550, \"voltage\": 0.5542, \"phase\": 2},\n",
    "    {\"ts\": datetime.now() + timedelta(hours=1), \"current\": 0.3550, \"voltage\": 0.5542, \"phase\": 3},\n",
    "]\n",
    "td.insert_many(table='meter_01', args=meters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "往单张表插入多条记录， 如果表不存在，就创建表"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "2"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meters = [\n",
    "    {\"ts\": \"2021-11-19 15:30:44.123445\", \"current\": 0.3550, \"voltage\": 0.5542, \"phase\": 0, \"location\": \"上海\", \"groupId\": 1},\n",
    "    {\"ts\": datetime.now() - timedelta(hours=1), \"current\": 0.3550, \"voltage\": 0.5542, \"phase\": 1, \"location\": \"上海\", \"groupId\": 1},\n",
    "]\n",
    "td.insert_many_with_stable(table='meter_02', stable=\"meters\", args=meters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "同时往多张表插入记录, 'meter_01', 'meter_02'表已经存在，可以不加stable；'meter_03'必须指定stable"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "3"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "meters = [\n",
    "    {\"table\": \"meter_01\", \"ts\": \"2021-11-19 17:30:43.1234\", \"current\": 0.3550, \"voltage\": 0.5542, \"phase\": 4, \"location\": \"北京\", \"groupId\": 0},\n",
    "    {\"table\": \"meter_02\", \"ts\": \"2021-11-19 17:30:43.1234\", \"current\": 0.3550, \"voltage\": 0.5542, \"phase\": 2, \"location\": \"上海\", \"groupId\": 1},\n",
    "    {\"table\": \"meter_03\", \"stable\": \"meters\", \"ts\": datetime.now(), \"current\": 0.3550, \"voltage\": 0.5542, \"phase\": 0, \"location\": \"天津\", \"groupId\": 2},\n",
    "]\n",
    "td.insert_many_tables(args=meters)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查询表中行数"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "9"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td.get(\"select count(1) from meters\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查询单值"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0.35499998927116394"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td.get(\"select last(current) from meters\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查询一行"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ts': datetime.datetime(2021, 11, 19, 17, 44, 4, 674000),\n",
       " 'current': 0.35499998927116394,\n",
       " 'voltage': 0.5541999936103821,\n",
       " 'phase': 3}"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td.select_one(\"select last_row(*) from meters\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "查询 list"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[{'ts': datetime.datetime(2021, 11, 19, 15, 43, 53, 753000),\n",
       "  'current': 0.2549999952316284,\n",
       "  'voltage': 0.35420000553131104,\n",
       "  'phase': 0,\n",
       "  'location': '北京',\n",
       "  'groupid': 0},\n",
       " {'ts': datetime.datetime(2021, 11, 19, 16, 33, 59, 777000),\n",
       "  'current': 0.35499998927116394,\n",
       "  'voltage': 0.5541999936103821,\n",
       "  'phase': 1,\n",
       "  'location': '北京',\n",
       "  'groupid': 0},\n",
       " {'ts': datetime.datetime(2021, 11, 19, 16, 43, 4, 674000),\n",
       "  'current': 0.35499998927116394,\n",
       "  'voltage': 0.5541999936103821,\n",
       "  'phase': 2,\n",
       "  'location': '北京',\n",
       "  'groupid': 0},\n",
       " {'ts': datetime.datetime(2021, 11, 19, 17, 30, 43, 123000),\n",
       "  'current': 0.35499998927116394,\n",
       "  'voltage': 0.5541999936103821,\n",
       "  'phase': 4,\n",
       "  'location': '北京',\n",
       "  'groupid': 0},\n",
       " {'ts': datetime.datetime(2021, 11, 19, 17, 44, 4, 674000),\n",
       "  'current': 0.35499998927116394,\n",
       "  'voltage': 0.5541999936103821,\n",
       "  'phase': 3,\n",
       "  'location': '北京',\n",
       "  'groupid': 0}]"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "td.select(\"select * from meters where location = '北京'\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
